﻿using FreeSql;
using FreeSql.Internal;
using FreeSql.Internal.CommonProvider;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;
using System.Data.Common;
using System.Reflection;

namespace FreeSql
{
    public static class FreeSqlPgSqlExtension
    {
        public static void AddSql(this IServiceCollection services, IConfigurationSection section)
        {

            var config = section.Get<DbConfig>() ?? new DbConfig();
            services.Configure<DbConfig>(section);
            DbConfig.DbBegDate = config.BegDate;
            IdHelper.SetIdGeneratorOptions(config.WorkerId, (byte)config.WorkerIdBitLength);
            IFreeSql fsql = new FreeSqlBuilder()
                .UseConnectionString(DataType.PostgreSQL, config.ConnectionString)
                 .UseNameConvert(NameConvertType.PascalCaseToUnderscoreWithLower)//oracle 全部转为大写
                .CreateDatabaseIfNotExistsPgSql()
                .UseMonitorCommand(
                    cmd =>
                    {
                        cmd.CommandTimeout = 9999;
                        if (config.WatchCurd)
                        {
                            var p = "";
                            foreach (DbParameter item in cmd.Parameters)
                            {
                                if (p == "") p += "\r\nParameters：\r\n";
                                p += ($"ParameterName:{item.ParameterName},Value:{item.Value}\r\n");
                            }
                            Serilog.Log.Logger.Debug("\r\n*******begin================SQL================**********\r\n" + cmd.CommandText + p + "\r\n*******end================SQL================**********");
                        }
                    })
                .UseNoneCommandParameter(config.NoneCommandParameter)
                //.UseAutoSyncStructure(config.SyncStructure) //自动同步实体结构到数据库，只有CRUD时才会生成表
                .Build();

            fsql.UseJsonMap();

            /* var selectProvider = fsql.Select<object>() as Select0Provider;
             var serverTime = fsql.Select<object>().WithSql($"select {selectProvider._commonUtils.NowUtc} a").First(a => Convert.ToDateTime("a"));
             var timeOffset = DateTime.UtcNow.Subtract(serverTime);*/

            fsql.Aop.AuditValue += (s, e) =>
            {
                DbHelper.AuditValue(e);
            };

            fsql.Aop.ConfigEntityProperty += (s, e) =>
            {
                DbHelper.ConfigEntityProperty(s, e);
            };
            fsql.GlobalFilter.Apply<IBaseSoftDelete>(nameof(IBaseSoftDelete), a => a.IsDeleted == false);



            services.AddSingleton<IFreeSql>(fsql);
            services.AddScoped<UnitOfWorkManager>();
            services.AddScoped(typeof(IRepositoryDefault<>), typeof(RepositoryDefault<>));
            services.AddScoped(typeof(IRepositoryBase<>), typeof(RepositoryBase<>));
            services.AddScoped(typeof(IRepositoryDefault<,>), typeof(RepositoryDefault<,>));
            services.AddScoped(typeof(IRepositoryBase<,>), typeof(RepositoryBase<,>));
            //services.AddScoped(typeof(IBaseRepository<>), typeof(GuidRepository<>));
            //services.AddScoped(typeof(BaseRepository<>), typeof(GuidRepository<>));
            //services.AddScoped(typeof(IBaseRepository<,>), typeof(DefaultRepository<,>));
            //services.AddScoped(typeof(BaseRepository<,>), typeof(DefaultRepository<,>));

        }

        #region PgSql
        private static string GetConnectionString(FreeSqlBuilder @this)
        {
            Type type = @this.GetType();
            FieldInfo? fieldInfo = type.GetField("_masterConnectionString", BindingFlags.NonPublic | BindingFlags.Instance);
            if (fieldInfo is null)
            {
                throw new ArgumentException("_masterConnectionString is null");
            }
            return fieldInfo?.GetValue(@this)?.ToString() ?? "";
        }
        public static FreeSqlBuilder CreateDatabaseIfNotExistsPgSql(this FreeSqlBuilder @this, FreeSql.DataType dataType, string connectionString = "")
        {
            if (dataType == DataType.PostgreSQL)
            {
                return CreateDatabaseIfNotExistsPgSql(@this, connectionString);
            }
            return @this;
        }
        public static FreeSqlBuilder CreateDatabaseIfNotExistsPgSql(this FreeSqlBuilder @this, string connectionString = "")
        {
            if (connectionString == "")
            {
                connectionString = GetConnectionString(@this);
            }

            NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder(connectionString);

            string databaseExistSql = $"SELECT COUNT(*) FROM pg_database WHERE datname = '{builder.Database}'";

            string createDatabaseSql = $"CREATE DATABASE \"{builder.Database}\" WITH OWNER = \"{builder.Username}\"";

            using NpgsqlConnection cnn = new NpgsqlConnection($"Host={builder.Host};Port={builder.Port};Username={builder.Username};Password={builder.Password};Pooling=true");
            cnn.Open();

            using (NpgsqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = databaseExistSql;

                var count = cmd.ExecuteScalar();
                bool databaseExist = Convert.ToInt64(count) > 0;
                if (databaseExist == false)
                {
                    cmd.CommandText = createDatabaseSql;
                    cmd.ExecuteNonQuery();
                }
            }


            return @this;
        }


        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="this"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        /// <exception cref="Exception"></exception>
        public static FreeSqlBuilder DropDatabaseIfNotExists(this FreeSqlBuilder @this)
        {
            FieldInfo? dataTypeFieldInfo = @this.GetType().GetField("_dataType", BindingFlags.NonPublic | BindingFlags.Instance);

            if (dataTypeFieldInfo is null)
            {
                throw new ArgumentException("_dataType is null");
            }

            string connectionString = GetConnectionString(@this);
            return @this.DropDatabaseIfNotExistsPgSql(connectionString);
            //    Log.Error($"不支持创建数据库");
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="this"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static FreeSqlBuilder DropDatabaseIfNotExistsPgSql(this FreeSqlBuilder @this, string connectionString = "")
        {
            if (connectionString == "")
            {
                connectionString = GetConnectionString(@this);
            }

            NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder(connectionString);

            string databaseExistSql = $"SELECT COUNT(*) FROM pg_database WHERE datname = '{builder.Database}'";

            string dropDatabaseSql = $"DEOP DATABASE \"{builder.Database}\" WITH OWNER = \"{builder.Username}\"";

            using NpgsqlConnection cnn = new NpgsqlConnection($"Host={builder.Host};Port={builder.Port};Username={builder.Username};Password={builder.Password};Pooling=true");
            cnn.Open();

            using (NpgsqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = databaseExistSql;
                bool databaseExist = Convert.ToInt64(cmd.ExecuteScalar()) > 0;
                if (databaseExist)
                {
                    cmd.CommandText = dropDatabaseSql;
                    cmd.ExecuteNonQuery();
                }
            }


            return @this;
        }




        #endregion

    }
}
